package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.PostServiceBean;
import com.peenongsaban.freelance.bean.RequestServiceBean;
import com.peenongsaban.freelance.bean.ServiceBean;
import com.peenongsaban.freelance.bean.StatusOfRequestBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.MyServiceDAO;
import com.peenongsaban.freelance.meta.PostServiceMeta;
import com.peenongsaban.freelance.meta.RequestTypeMeta;
import com.peenongsaban.freelance.meta.ServiceMeta;
import com.peenongsaban.freelance.meta.StatusOfRequestMeta;
import com.peenongsaban.freelance.meta.ViewMeta;
import com.peenongsaban.freelance.model.MyServiceModel;
import com.peenongsaban.freelance.utils.ConstantUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;
import com.peenongsaban.freelance.utils.StringUtils;

public class MyServiceDAOImpl implements MyServiceDAO {

	@Override
	public Vector<PostServiceBean> listMyPostService(MyServiceModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostServiceBean> vector = new Vector<PostServiceBean>();
		PreparedStatement statement = null;
		boolean condition1 = false;
		try {
			String sql = "SELECT * FROM " + ViewMeta.LIST_POST_SERVICE_V
					+ " WHERE " + PostServiceMeta.COL_ID_CARD + " = ? ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByPostService())) {
				sql += " AND " + PostServiceMeta.COL_STATUS_OF_POST + " = ?";
				condition1 = true;
			}
			// Condition SQL command for paging
			int itemList = ConstantUtils.LIMIT_PAGING_MY_POST_SERVICE;
			if (model.getPagePostService().getPageCurrentNo() == 0
					|| model.getPagePostService().getPageCurrentNo() == 1) {
				sql += " LIMIT 0," + itemList;
			} else {
				sql += " LIMIT "
						+ ((model.getPagePostService().getPageCurrentNo() - 1) * itemList)
						+ "," + itemList;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			if (condition1) {
				statement.setString(2, model.getOrderByPostService());
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostServiceBean bean = new PostServiceBean();
				bean.setPostID(rs.getString(PostServiceMeta.COL_POST_ID));
				bean.getUser().setIdCard(
						rs.getString(PostServiceMeta.COL_ID_CARD));
				bean.setPostDate(rs.getDate(PostServiceMeta.COL_POST_DATE));
				bean.getService().setServiceName(
						rs.getString(ServiceMeta.COL_SERVICE_NAME));
				bean.setAmountRequestPost(rs
						.getInt(PostServiceMeta.COL_AMOUNT_REQUEST_POST));
				bean.setStatusOfPost(rs
						.getString(PostServiceMeta.COL_STATUS_OF_POST));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<RequestServiceBean> listMyRequestService(MyServiceModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<RequestServiceBean> vector = new Vector<RequestServiceBean>();
		PreparedStatement statement = null;
		boolean condition1 = false;
		try {
			String sql = "SELECT * FROM " + ViewMeta.LIST_MY_REQUEST_SERVICE_V
					+ " WHERE " + PostServiceMeta.COL_ID_CARD + " = ? ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByRequestService())) {
				sql += " AND " + StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID
						+ " = ?";
				condition1 = true;
			}
			// Condition SQL command for paging
			int itemList = ConstantUtils.LIMIT_PAGING_MY_REQUEST_SERVICE;
			if (model.getPageRequestService().getPageCurrentNo() == 0||model.getPageRequestService().getPageCurrentNo() == 1) {
				sql += " LIMIT 0," + itemList;
			} else {
				sql += " LIMIT "
						+ ((model.getPageRequestService().getPageCurrentNo() - 1) * itemList)
						+ "," + itemList;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			if (condition1) {
				statement.setString(2, model.getOrderByRequestService());
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				RequestServiceBean bean = new RequestServiceBean();
				bean.getPostService().setPostID(
						rs.getString(PostServiceMeta.COL_POST_ID));
				bean.getUser().setIdCard(rs.getString(PostServiceMeta.COL_ID_CARD));
				bean.getPostService().setPostDate(
						rs.getDate(PostServiceMeta.COL_POST_DATE));
				bean.getPostService().getService()
						.setServiceName(rs.getString(ServiceMeta.COL_SERVICE_NAME));

				Vector<StatusOfRequestBean> vecStatus = new Vector<StatusOfRequestBean>();
				StatusOfRequestBean statusBean = new StatusOfRequestBean();
				statusBean.getRequestType().setRequestTypeName(rs
						.getString(RequestTypeMeta.COL_REQUEST_TYPE_NAME));
				statusBean
						.setStatusOfRequestID(rs
								.getString(StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID));
				statusBean.setDateTime(rs
						.getDate(StatusOfRequestMeta.COL_DATE_TIME));
				vecStatus.add(statusBean);
				bean.setListStatusOfRequest(vecStatus);

				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	
	}

	@Override
	public Vector<PostServiceBean> listLastPostService() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostServiceBean> vector = new Vector<PostServiceBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT " + PostServiceMeta.COL_POST_ID + ","
					+ ServiceMeta.COL_SERVICE_NAME + " FROM "
					+ ViewMeta.LIST_POST_SERVICE_V + " ORDER BY "
					+ PostServiceMeta.COL_POST_DATE + " DESC LIMIT 0,9";
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();

			databaseConnection.freeConnection(conn);

			while (rs.next()) {
				PostServiceBean post = new PostServiceBean();
				post.setPostID(rs.getString(PostServiceMeta.COL_POST_ID));

				ServiceBean service = new ServiceBean();
				service.setServiceName(rs
						.getString(ServiceMeta.COL_SERVICE_NAME));

				post.setService(service);
				vector.add(post);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				if (statement != null)
					statement.close();

				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public int countLlistMyPostService(MyServiceModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		boolean condition1 = false;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_POST_SERVICE_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByPostService())) {
				sql += " AND " + PostServiceMeta.COL_STATUS_OF_POST + " = ?";
				condition1 = true;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			if (condition1) {
				statement.setString(2, model.getOrderByPostService());
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}

	@Override
	public int countLlistMyRequestService(MyServiceModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		boolean condition1 = false;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_POST_SERVICE_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? ";
			if (!StringUtils.isEmptyOrNull(model.getOrderByPostService())) {
				sql += " AND " + PostServiceMeta.COL_STATUS_OF_POST + " = ?";
				condition1 = true;
			}
			statement = conn.prepareStatement(sql);
			statement.setString(1, model.getUser().getIdCard());
			if (condition1) {
				statement.setString(2, model.getOrderByPostService());
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}

	@Override
	public int countAllMyPostService(String idCard) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_POST_SERVICE_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, idCard);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}

	@Override
	public int countAllMyRequestService(String idCard) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		int size = 0;
		try {
			String sql = "SELECT COUNT(*) AS CNT FROM "
					+ ViewMeta.LIST_MY_REQUEST_SERVICE_V + " WHERE "
					+ PostServiceMeta.COL_ID_CARD + " = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, idCard);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}

}
